﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using DTO;

namespace DAO
{
    public class SinhVien_DAO
    {
        // load danh sach tat ca sinh vien
        public static DataTable loadTatCaSinhVien()
        {
            SqlConnection cnn = dbConnection.hamKetNoi();
            SqlCommand cmd = new SqlCommand("spLoadTatCaSinhVien", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            return dtb;
        }
        // lay thong tin 1 sinh vien
        public static DataTable layThongTinSinhVien(string MaSV)
        {
            SqlConnection cnn = dbConnection.hamKetNoi();
            SqlCommand cmd = new SqlCommand("spLayThongTinSinhVien", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@MaSV", SqlDbType.NChar, 10);
            cmd.Parameters["@MaSV"].Value = MaSV;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            return dtb;
        }
        // kiem tra xem ma sinh vien da ton tai chua
        public static bool kiemTraMaSV(string masv)
        {
            int exist = 0;
            SqlConnection cnn = dbConnection.hamKetNoi();
            SqlCommand cmd = new SqlCommand("spKiemTraMaSV", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            //
            cmd.Parameters.Add("@MaSV", SqlDbType.NChar, 10);
            //
            cmd.Parameters["@MaSV"].Value = masv;
            //
            if (cnn.State == ConnectionState.Open)
                cnn.Close();
            cnn.Open();
            exist = (int)cmd.ExecuteScalar();
            cnn.Close();
            if (exist > 0)
                return true;
            return false;
        }
        // them moi sinh vien
        public static void themMoiSinhVien(SinhVien_DTO sv, KhoaHoc_DTO kh)
        {
            SqlConnection cnn = dbConnection.hamKetNoi();
            SqlCommand cmd = new SqlCommand("spThemSinhVien", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            //
            cmd.Parameters.Add("@MaSV", SqlDbType.NChar, 10);
            cmd.Parameters.Add("@HoTen", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@NgSinh", SqlDbType.NChar, 15);
            cmd.Parameters.Add("@NoiSinh", SqlDbType.NChar, 20);
            cmd.Parameters.Add("@GioiTinh", SqlDbType.NChar, 10);
            cmd.Parameters.Add("@DanToc", SqlDbType.NChar, 15);
            cmd.Parameters.Add("@SoCMND", SqlDbType.NChar, 15);
            cmd.Parameters.Add("@LoaiSV", SqlDbType.NChar, 30);
            cmd.Parameters.Add("@TinhTrang", SqlDbType.NChar, 15);
            cmd.Parameters.Add("@MaKhoa", SqlDbType.NChar, 10);
            cmd.Parameters.Add("@DiaChi", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@DienThoai", SqlDbType.NChar, 15);
            cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@GhiChu", SqlDbType.NText);
            //
            cmd.Parameters.Add("@KhoaHoc", SqlDbType.NChar, 10);
            cmd.Parameters.Add("@NienKhoa", SqlDbType.NChar, 15);
            cmd.Parameters.Add("@NamNhapHoc", SqlDbType.Int);
            cmd.Parameters.Add("@NamKetThuc", SqlDbType.Int);
            cmd.Parameters.Add("@ChuongTrinhDaoTao", SqlDbType.NChar, 10);
            cmd.Parameters.Add("@Lop", SqlDbType.NChar, 10);
            cmd.Parameters.Add("@CVHT", SqlDbType.NChar, 10);
            // GÁN GIÁ TRỊ
            cmd.Parameters["@MaSV"].Value = sv.MaSV;
            cmd.Parameters["@HoTen"].Value = sv.HoTen;
            cmd.Parameters["@NgSinh"].Value = sv.NgSinh;
            cmd.Parameters["@NoiSinh"].Value = sv.NoiSinh;
            cmd.Parameters["@GioiTinh"].Value = sv.GioiTinh;
            cmd.Parameters["@DanToc"].Value = sv.DanToc;
            cmd.Parameters["@SoCMND"].Value = sv.SoCMND;
            cmd.Parameters["@LoaiSV"].Value = sv.LoaiSV;
            cmd.Parameters["@TinhTrang"].Value = sv.TinhTrang;
            cmd.Parameters["@MaKhoa"].Value = sv.MaKhoa;
            cmd.Parameters["@DiaChi"].Value = sv.DiaChi;
            cmd.Parameters["@DienThoai"].Value = sv.DienThoai;
            cmd.Parameters["@Email"].Value = sv.Email;
            cmd.Parameters["@GhiChu"].Value = sv.GhiChu;
            //
            cmd.Parameters["@KhoaHoc"].Value = kh.KhoaHoc;
            cmd.Parameters["@NienKhoa"].Value = kh.NienKhoa;
            cmd.Parameters["@NamNhapHoc"].Value = kh.NamNhapHoc;
            cmd.Parameters["@NamKetThuc"].Value = kh.NamKetThuc;
            cmd.Parameters["@ChuongTrinhDaoTao"].Value = kh.ChuongTrinhDaoTao;
            cmd.Parameters["@Lop"].Value = kh.Lop;
            cmd.Parameters["@CVHT"].Value = kh.CVHT;
            //
            if (cnn.State == ConnectionState.Open)
                cnn.Close();
            cnn.Open();
            cmd.ExecuteNonQuery();
            cnn.Close();
        }
        public static void suaThongTinSinhVien(SinhVien_DTO sv, KhoaHoc_DTO kh)
        {
            SqlConnection cnn = dbConnection.hamKetNoi();
            SqlCommand cmd = new SqlCommand("spSuaThongTinSinhVien", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            //
            cmd.Parameters.Add("@MaSV", SqlDbType.NChar, 10);
            cmd.Parameters.Add("@HoTen", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@NgSinh", SqlDbType.NChar, 15);
            cmd.Parameters.Add("@NoiSinh", SqlDbType.NChar, 20);
            cmd.Parameters.Add("@GioiTinh", SqlDbType.NChar, 10);
            cmd.Parameters.Add("@DanToc", SqlDbType.NChar, 15);
            cmd.Parameters.Add("@SoCMND", SqlDbType.NChar, 15);
            cmd.Parameters.Add("@LoaiSV", SqlDbType.NChar, 30);
            cmd.Parameters.Add("@TinhTrang", SqlDbType.NChar, 15);
            cmd.Parameters.Add("@MaKhoa", SqlDbType.NChar, 10);
            cmd.Parameters.Add("@DiaChi", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@DienThoai", SqlDbType.NChar, 15);
            cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@GhiChu", SqlDbType.NText);
            //
            cmd.Parameters.Add("@KhoaHoc", SqlDbType.NChar, 10);
            cmd.Parameters.Add("@NienKhoa", SqlDbType.NChar, 15);
            cmd.Parameters.Add("@NamNhapHoc", SqlDbType.Int);
            cmd.Parameters.Add("@NamKetThuc", SqlDbType.Int);
            cmd.Parameters.Add("@ChuongTrinhDaoTao", SqlDbType.NChar, 10);
            cmd.Parameters.Add("@Lop", SqlDbType.NChar, 10);
            cmd.Parameters.Add("@CVHT", SqlDbType.NChar, 10);
            // GÁN GIÁ TRỊ
            cmd.Parameters["@MaSV"].Value = sv.MaSV;
            cmd.Parameters["@HoTen"].Value = sv.HoTen;
            cmd.Parameters["@NgSinh"].Value = sv.NgSinh;
            cmd.Parameters["@NoiSinh"].Value = sv.NoiSinh;
            cmd.Parameters["@GioiTinh"].Value = sv.GioiTinh;
            cmd.Parameters["@DanToc"].Value = sv.DanToc;
            cmd.Parameters["@SoCMND"].Value = sv.SoCMND;
            cmd.Parameters["@LoaiSV"].Value = sv.LoaiSV;
            cmd.Parameters["@TinhTrang"].Value = sv.TinhTrang;
            cmd.Parameters["@MaKhoa"].Value = sv.MaKhoa;
            cmd.Parameters["@DiaChi"].Value = sv.DiaChi;
            cmd.Parameters["@DienThoai"].Value = sv.DienThoai;
            cmd.Parameters["@Email"].Value = sv.Email;
            cmd.Parameters["@GhiChu"].Value = sv.GhiChu;
            //
            cmd.Parameters["@KhoaHoc"].Value = kh.KhoaHoc;
            cmd.Parameters["@NienKhoa"].Value = kh.NienKhoa;
            cmd.Parameters["@NamNhapHoc"].Value = kh.NamNhapHoc;
            cmd.Parameters["@NamKetThuc"].Value = kh.NamKetThuc;
            cmd.Parameters["@ChuongTrinhDaoTao"].Value = kh.ChuongTrinhDaoTao;
            cmd.Parameters["@Lop"].Value = kh.Lop;
            cmd.Parameters["@CVHT"].Value = kh.CVHT;
            //
            if (cnn.State == ConnectionState.Open)
                cnn.Close();
            cnn.Open();
            cmd.ExecuteNonQuery();
            cnn.Close();

        }
        public static void xoaSinhVien(string masv)
        {
            SqlConnection cnn = dbConnection.hamKetNoi();
            SqlCommand cmd = new SqlCommand("spXoaMotSinhVien");
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@MaSV", SqlDbType.NChar, 10);
            cmd.Parameters["@MaSV"].Value = masv;
            //
            if(cnn.State==ConnectionState.Open)
            {
                cnn.Close();
            }
            cnn.Open();
            cmd.ExecuteNonQuery();
            cnn.Close();
        }
    }
}
